/**
 * 
 */
package com.feib.stms.dao.jdbc;

import java.sql.Types;

import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.stereotype.Repository;

import com.feib.stms.dao.TempDeliveryPaymentDao;
import com.feib.stms.model.TempDeliveryPayment;
import com.feib.stms.model.TempUploadFile;

/**
 * @author Jimmy Liu
 *
 */
@Repository("tempDeliveryPaymentDao")
public class TempDeliveryPaymentJdbcDao extends BaseTempPaymentJdbcDao<TempDeliveryPayment> implements TempDeliveryPaymentDao {
	
	private String sequenceName = "TEMP_DELIVERY_PAYMENT_PK_SEQ";
	
	private String tableName = "TEMP_DELIVERY_PAYMENT";
	
	private String tablePkName = "TEMP_DELIVERY_PAYMENT_PK";

	@Override
	protected String getSequenceName() {
		return sequenceName;
	}

	@Override
	protected String getTableName() {
		return tableName;
	}

	@Override
	protected String getTablePkName() {
		return tablePkName;
	}
	@Override
	protected void setTablePKValue(TempDeliveryPayment baseTempPayment, long pkValue) {
		baseTempPayment.setTempDeliveryPaymentPk(pkValue);		
	}

	public int copyToTempDeliveryPamtSum(TempUploadFile tempUploadFile) {

		/* 新增主檔 */
		Long tempUploadFilePk = tempUploadFile.getTempUploadFilePk();
		
		StringBuffer sql = new StringBuffer();
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		sql.append("INSERT INTO TEMP_DELIVERY_SUM (");
		sql.append("TEMP_DELIVERY_SUM_PK, TEMP_UPLOAD_FILE_PK, GROUP_PK, ACCOUNT_NO, DELIVERY_DATE, NET_AMOUNT");
		sql.append(", DEAL_TYPE, CUSTOMER_NAME, CUSTOMER_ID, TELLER, SECURITIES_NO");
		sql.append(", UPDATED_DATE, UPDATED_BY, CREATED_DATE, CREATED_BY");
		sql.append(")");
		
		sql.append("SELECT ");
		sql.append("TEMP_DELIVERY_SUM_PK_SEQ.NEXTVAL, T1.TEMP_UPLOAD_FILE_PK, T1.GROUP_PK, T1.ACCOUNT_NO, T1.DELIVERY_DATE, T1.NET_AMOUNT");
		sql.append(", T1.DEAL_TYPE, T1.CUSTOMER_NAME, T1.CUSTOMER_ID, T1.TELLER, T1.SECURITIES_NO");
		sql.append(", SYSDATE, T1.CREATED_BY, SYSDATE, T1.CREATED_BY");
		sql.append(" FROM ( ");
		
		sql.append("SELECT ");
		sql.append("T.TEMP_UPLOAD_FILE_PK, T.GROUP_PK, T.ACCOUNT_NO, T.DELIVERY_DATE, SUM(T.NET_INCOME -T.NET_PAY) AS NET_AMOUNT");
		sql.append(", T.DEAL_TYPE, T.CUSTOMER_NAME, T.CUSTOMER_ID, T.TELLER, T.SECURITIES_NO");
		sql.append(", T.CREATED_BY");
		sql.append(" FROM TEMP_DELIVERY_PAYMENT T ");
		sql.append(" WHERE T.TEMP_UPLOAD_FILE_PK = :TEMP_UPLOAD_FILE_PK ");
		sql.append(" GROUP BY T.TEMP_UPLOAD_FILE_PK, T.GROUP_PK, T.ACCOUNT_NO, T.DELIVERY_DATE");
		sql.append(", T.DEAL_TYPE, T.CUSTOMER_NAME, T.CUSTOMER_ID, T.TELLER, T.SECURITIES_NO, T.CREATED_BY");
		sql.append(") T1 ");

		parameters.addValue("TEMP_UPLOAD_FILE_PK", tempUploadFilePk, Types.NUMERIC);

		return this.getSimpleJdbcTemplate().update(sql.toString(), parameters);
	}

}
